package com.sunducation.wts.room.dao

import android.database.Cursor
import androidx.room.*
import com.sunducation.wts.room.bean.Book
import com.sunducation.wts.room.tuple.NameTuple
import com.sunducation.wts.room.bean.User
import io.reactivex.Completable
import io.reactivex.Flowable
import io.reactivex.Single

@Dao
interface UserDao {
    @Query("SELECT * FROM TABLE_USER")
    fun getAll(): List<User>

    @Query("SELECT * FROM TABLE_USER WHERE userId IN (:userIds)")
    fun loadAllByIds(userIds: IntArray): List<User>

    @Query("SELECT * FROM TABLE_USER WHERE userId = (:id) LIMIT 1")
    fun queryUserById(id: Long): User

    @Query("SELECT * FROM TABLE_USER WHERE first_name LIKE :first AND " +
            "lastname LIKE :last LIMIT 1")
    fun findByName(first: String, last: String): User

    @Query("SELECT * FROM TABLE_USER WHERE userId > :minAge")
    fun loadAllUsersOlderThan(minAge: Int): Array<User>

    @Query("SELECT * FROM TABLE_USER WHERE userId BETWEEN :minAge AND :maxAge")
    fun loadAllUsersBetweenAges(minAge: Int, maxAge: Int): Array<User>

    @Query("SELECT * FROM TABLE_USER WHERE first_name LIKE :search " +
            "OR lastName LIKE :search")
    fun findUserWithName(search: String): List<User>

    @Query("SELECT first_name, lastName FROM TABLE_USER")
    fun loadFullName(): List<NameTuple> //返回子集

    @Insert
    fun insertAll(vararg users: User)

    @Insert(onConflict = OnConflictStrategy.REPLACE) //如果有相同的id就替换； ABORT默认这个，如果相同，会报错
    fun insertUsers(vararg users: User)

    @Insert
    fun insertBothUsers(user1: User, user2: User)

    @Insert
    fun insertUsersAndFriends(user: User, friends: List<User>)

    @Delete
    fun delete(user: User)

    @Delete
    fun deleteAll(vararg users: User)

    @Update
    fun updateUsers(vararg users: User)




    @Query("SELECT * from TABLE_USER where userId = :id LIMIT 1")
    fun loadUserById(id: Int): Flowable<User>

    @Query("SELECT * FROM TABLE_USER WHERE userId > :minAge LIMIT 5")
    fun loadRawUsersOlderThan(minAge: Int): Cursor

    /*@Insert
    fun insertLargeNumberOfUsers(users: List<User>): Maybe<Int?>*/

    @Insert
    fun insertLargeNumberOfUsers2(vararg users: User): Completable

    @Delete
    fun deleteAllUsers(users: List<User>): Single<Int>

    //查询多张表
    @Query("SELECT * FROM BOOK "
            + "INNER JOIN TABLE_USER ON TABLE_USER.userId = BOOK.user_id "
            + "WHERE TABLE_USER.first_name LIKE :userName")
    fun  findBooksBorrowedByNameSync(userName: String): List<Book>
}